package com.pms.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import com.pms.entity.Goal;
import com.pms.entity.Project;
import com.pms.entity.UserProject;
import com.pms.util.DBHelp;
import com.pms.util.Page;
import com.pms.util.RowMapper;

public class GoalDao {
	DBHelp<Goal> db = new DBHelp<Goal>();
	
	public Long getCount(String projectId) {
		String sql = "SELECT COUNT(*) FROM t_goal where projectid = ?";
		return (Long) db.executeQueryForFunction(sql,projectId);
	}

	public Page<Goal> findAll(Page<Goal> page, UserProject up) {
		StringBuilder sql=new StringBuilder();
		sql.append("SELECT goalid,goalname,tg.`desc`,tg.createtime,tg.projectid,tp.projectname,tp.desc,tp.createtime, ");
		sql.append("(SELECT SUM(taskrate)/COUNT(*)  FROM t_task WHERE goalid = tg.goalid) as goalRate from t_goal as tg ");
		sql.append("LEFT JOIN t_project AS tp ON tg.projectid = tp.projectid ");
		sql.append("where tg.projectid = ? order by tg.createtime desc limit ?,?");
		List<Goal> list = db.executeQueryForList(sql.toString(), new GoalRowMapper(),up.getProjectId(), page.getOffSet(),page.getPageSize());
		page.setResult(list);
		return page;
	}
	
	

	public boolean save(Goal goal) {
		String sql = "INSERT INTO t_goal(goalid,goalname,`desc`,createtime,projectid) VALUES(?,?,?,?,?)";
		return db.executeSQL(sql, goal.getGoalId(),goal.getGoalName(),goal.getDesc(),goal.getCreateTime(),goal.getProjectId());
		
	}

	public Goal findById(String goalId) {
		StringBuilder sql=new StringBuilder();
		sql.append("SELECT goalid,goalname,tg.`desc`,tg.createtime,tg.projectid,tp.projectname,tp.desc,tp.createtime  FROM t_goal AS tg ");
		sql.append("LEFT JOIN t_project AS tp ON tg.projectid = tp.projectid ");
		sql.append("WHERE goalid = ?");
		return db.executeQueryForObject(sql.toString(), new GoalRowMapper(), goalId);
	}
	
	
	
	private class GoalRowMapper implements RowMapper<Goal>{
		

		@Override
		public Goal mapRow(ResultSet rs) throws SQLException {
			Goal goal = new Goal();
			goal.setCreateTime(rs.getString("createTime"));
			goal.setDesc(rs.getString("desc"));
			goal.setGoalId(rs.getString("goalId"));
			goal.setGoalName(rs.getString("goalName"));
			//goal.setGoalRate(rs.getInt("goalRate"));
			
			Project project=new Project();
			project.setProjectId(rs.getString("projectId"));
			project.setProjectName(rs.getString("projectName"));
			project.setDesc(rs.getString("desc"));
			project.setCreateTime(rs.getString("createTime"));
			
			goal.setProject(project);
			goal.setProjectId(rs.getString("projectId"));
			
			return goal;
		}
		
	}



	public boolean update(String goalId, String goalName, String desc) {
		String sql = "UPDATE t_goal SET goalname = ?,`desc` = ? WHERE goalid = ?";
		return db.executeSQL(sql, goalName,desc,goalId);
	}

}
